from sqlalchemy import select, func, exc, and_, or_, union_all
from app.model.BusinessInfoModel import BusinessInfo
from app.model.RiskHistoryModel import RiskHistory
from app.config.db import DBUtils


class SearchService:

    def __init__(self):
        self.db_utils = DBUtils()

    # 新增方法：根据 Name 进行模糊查询并返回前十五条数据
    def select_companyName(self, session, searchInput, resultcount, pagenum, gs):
        try:
            # 计算偏移量
            offset = (int(pagenum) - 1) * int(resultcount)
            subquery = union_all(
                select(RiskHistory.CYDW.label("GSMC"), func.count(RiskHistory.SXLX).label("total_count"))
                .group_by(RiskHistory.CYDW),

                select(RiskHistory.JYDS.label("GSMC"), func.count(RiskHistory.SXLX).label("total_count"))
                .group_by(RiskHistory.JYDS)
            ).subquery()
            query = (
                select(BusinessInfo, func.coalesce(subquery.c.total_count, 0).label('total_count'))
                .outerjoin(subquery, BusinessInfo.GSMC == subquery.c.GSMC)
                .where(and_(BusinessInfo.GSMC.like(f'%{searchInput}%'),
                            or_(BusinessInfo.GS.is_(None),gs == '', BusinessInfo.GS == gs, gs == '集团总部', gs == '财务公司')))
                .order_by(BusinessInfo.ID)
                .limit(resultcount)
                .offset(offset)
            )
            results_list = session.execute(query).all()
            results = [{**item.to_dict(), "risk_total": total}
                            for item, total in results_list]
            # 获取总记录数（用于前端分页显示）
            total_count = (
                select(func.count())
                .select_from(BusinessInfo)
                .where(and_(BusinessInfo.GSMC.like(f'%{searchInput}%'),
                            or_(BusinessInfo.GS.is_(None),gs == '', BusinessInfo.GS == gs, gs == '集团总部', gs == '财务公司')))
            )
            totalcount = session.execute(total_count).scalar_one()

            res_data = {
                "total": totalcount,
                "results": results
            }
            if res_data:
                return res_data
            else:
                return []
        except exc.SQLAlchemyError as e:
            error_response = [{'error': str(e)}]
            try:
                self.db_utils.rollback()
            except Exception as rollback_error:
                print(f"Failed to rollback transaction: {rollback_error}")
            return error_response
        finally:
            try:
                self.db_utils.remove_session()
            except Exception as remove_error:
                print(f"Failed to rollback transaction: {remove_error}")
    # 历史风险总数
    # def select_risk_history_total(self, session, searchInput):
    #     try:
    #         query = (
    #             select(RiskHistory.GSMC,
    #                    func.sum(RiskHistory.GSMC).label('total'))
    #                    .where(RiskHistory.GSMC.in_(searchInput))
    #                    .group_by(RiskHistory.GSMC)
    #         )
    #         results_list = session.execute(query).scalars().all()
    #         result = {
    #             item.GSMC: item.total for item in results_list
    #         }
    #         return result
    #     finally:
    #         self.db_utils.remove_session()


